* collapse plants to county and merge with county-level establishment data

import excel using "/projects/users/########/uswtdb_public_070318_pid_csm.xlsx", ///
	clear first sheet("Data")
	
replace p_year=. if p_year == -9999
replace p_cap=. if p_cap == -9999
replace p_tnum=. if p_tnum == -9999
replace t_cap=. if t_cap == -9999
replace t_hh=. if t_hh == -9999

keep if substr(p_name,1,7) != "unknown"
keep if p_year >=1995 & p_year !=.
keep if t_state!="AK" & t_state!="PR" & t_state!="GU" & t_state != "HI"
keep if p_tnum <1400 & p_tnum > 7 & p_cap >2 

* collapse to plants, then to county
preserve
collapse p_tnum p_cap t_cap t_hh xlong ylat (firstnm) p_year t_state t_county t_fips p_name, by(csm_id)
sort csm_id
merge 1:1 csm_id using "/projects/users/########/capexCSM.dta"
keep if _merge==1 | _merge ==3
drop State _merge
gen capx = cap_cost_dolkw*1000*p_cap
label var capx "Millions $"

collapse (sum) c_ptnum = p_tnum c_pcap = p_cap c_capx = capx ///
		(firstnm) t_state t_county, by(t_fips p_year)
label var c_ptnum "Num Turbines from Plants Centered in Cnty"
label var c_pcap "Capacity from Plants Centered in Cnty"
save "/projects/users/########/CountyLevelWindDatabaseP.dta", replace
restore

* get turbine data at county level
preserve
collapse (sum) c_tcap = t_cap (count) c_tnum = t_cap ///
	(mean)  xlong ylat c_tp_cap = p_cap c_tp_tnum = p_tnum ///
	(firstnm) t_state t_county, by(t_fips p_year)
label var c_tnum "Num Turbines in Cnty"
label var c_tcap "Capacity in Cnty"
label var c_tp_tnum "Num Turbines from Plants Partially in Cnty"
label var c_tp_cap "Capacity from Plants Partially in Cnty"
save "/projects/users/########/CountyLevelWindDatabaseT.dta", replace
restore

* merge all together
use "/projects/users/########/CountyLevelWindDatabaseT.dta", clear
merge 1:1 t_fips p_year using "/projects/users/########/CountyLevelWindDatabaseP.dta", ///
	keepusing(c_ptnum c_pcap c_capx) 
drop _merge

destring(t_fips), gen(cnty)
gen year = p_year
sort cnty year
xtset cnty year
tsfill, full
recode c_tcap (mis=0)
recode c_tnum (mis=0)
recode c_tp_cap (mis=0)
recode c_tp_tnum (mis=0)
recode c_ptnum (mis=0)
recode c_pcap (mis=0)
recode c_capx (mis=0)
drop t_fips
gen t_fips = string(cnty,"%05.0f")
merge m:m t_fips using "/projects/users/########/CountyLevelWindDatabaseT.dta", ///
	 update nogen keepusing(xlong ylat t_state t_county)
bysort cnty (year): replace c_tcap = sum(c_tcap)
bysort cnty (year): replace c_tnum = sum(c_tnum)
bysort cnty (year): replace c_tp_cap = sum(c_tp_cap)
bysort cnty (year): replace c_tp_tnum = sum(c_tp_tnum)
bysort cnty (year): replace c_ptnum = sum(c_ptnum)
bysort cnty (year): replace c_pcap = sum(c_pcap)
bysort cnty (year): gen ct_capx = sum(c_capx)
rename t_fips ctyfips
save "/projects/users/########/CountyLevelWindDatabase.dta", replace



